<!DOCTYPE html>
<html lang='zh-CN'>

<head>
  <meta name="generator" content="Hexo 6.3.0">
  <meta name="hexo-theme" content="https://github.com/xaoxuu/hexo-theme-stellar/tree/1.19.0">
  <meta charset="utf-8">
  

  <meta http-equiv='x-dns-prefetch-control' content='on' />
  <link rel='dns-prefetch' href='https://gcore.jsdelivr.net'>
  <link rel="preconnect" href="https://gcore.jsdelivr.net" crossorigin>
  <link rel='dns-prefetch' href='//unpkg.com'>

  <meta name="renderer" content="webkit">
  <meta name="force-rendering" content="webkit">
  <meta http-equiv="X-UA-Compatible" content="IE=Edge,chrome=1">
  <meta name="HandheldFriendly" content="True" >
  <meta name="apple-mobile-web-app-capable" content="yes">
  <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
  <meta name="theme-color" content="#f8f8f8">
  
  <title>SQL语法基础 - 愔颂</title>

  
    <meta name="description" content="SQL语法基础练习与掌握">
<meta property="og:type" content="article">
<meta property="og:title" content="SQL语法基础">
<meta property="og:url" content="https://farhills.gitee.io/2023/10/14/SQL%E8%AF%AD%E6%B3%95%E5%9F%BA%E7%A1%80/index.html">
<meta property="og:site_name" content="愔颂">
<meta property="og:description" content="SQL语法基础练习与掌握">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="https://cdn.tobebetterjavaer.com/tobebetterjavaer/images/sidebar/sanfene/mysql-47ddea92-cf8f-49c4-ab2e-69a829ff1be2.jpg">
<meta property="article:published_time" content="2023-10-14T09:33:54.397Z">
<meta property="article:modified_time" content="2023-10-15T03:23:42.395Z">
<meta property="article:author" content="远岫">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="https://cdn.tobebetterjavaer.com/tobebetterjavaer/images/sidebar/sanfene/mysql-47ddea92-cf8f-49c4-ab2e-69a829ff1be2.jpg">
  
  
  
  

  <!-- feed -->
  

  
    
<link rel="stylesheet" href="/css/main.css">

  

  
    <link rel="shortcut icon" href="https://z1.ax1x.com/2023/10/05/pPXijyT.png">
  

  

  


  
</head>

<body>
  




  <div class='l_body' id='start'>
    <aside class='l_left' layout='post'>
    

  

<header class="header"><div class="logo-wrap"><a class="avatar" href="/about/"><div class="bg" style="opacity:0;background-image:url(https://gcore.jsdelivr.net/gh/cdn-x/placeholder@1.0.4/avatar/round/rainbow64@3x.webp);"></div><img no-lazy class="avatar" src="https://s1.ax1x.com/2022/11/12/ziJjfK.jpg" onerror="javascript:this.classList.add('error');this.src='https://gcore.jsdelivr.net/gh/cdn-x/placeholder@1.0.4/image/2659360.svg';"></a><a class="title" href="/"><div class="main" ff="title">愔颂</div></a></div>

<nav class="menu dis-select"><a class="nav-item active" href="/">文章</a><a class="nav-item" href="/friends/">收藏</a><a class="nav-item" href="/about/">关于</a></nav>
</header>


<div class="widgets">
<widget class="widget-wrapper search"><div class="widget-body"><div class="search-wrapper" id="search"><form class="search-form"><input type="text" class="search-input" id="search-input" data-filter="/blog/" placeholder="文章搜索"><svg t="1670596976048" class="icon search-icon" viewBox="0 0 1024 1024" version="1.1" xmlns="http://www.w3.org/2000/svg" p-id="2676" width="200" height="200"><path d="M938.2 832.6L723.8 618.1c-2.5-2.5-5.3-4.4-7.9-6.4 36.2-55.6 57.3-121.8 57.3-193.1C773.3 222.8 614.6 64 418.7 64S64 222.8 64 418.6c0 195.9 158.8 354.6 354.6 354.6 71.3 0 137.5-21.2 193.2-57.4 2 2.7 3.9 5.4 6.3 7.8L832.5 938c14.6 14.6 33.7 21.9 52.8 21.9 19.1 0 38.2-7.3 52.8-21.8 29.2-29.1 29.2-76.4 0.1-105.5M418.7 661.3C284.9 661.3 176 552.4 176 418.6 176 284.9 284.9 176 418.7 176c133.8 0 242.6 108.9 242.6 242.7 0 133.7-108.9 242.6-242.6 242.6" p-id="2677"></path></svg></form><div id="search-result"></div><div class="search-no-result">没有找到内容！</div></div></div></widget>


<widget class="widget-wrapper toc single" id="data-toc"><div class="widget-header cap dis-select"><span class="name">SQL语法基础</span></div><div class="widget-body fs14"><div class="doc-tree active"><ol class="toc"><li class="toc-item toc-level-3"><a class="toc-link" href="#1%E3%80%81%E4%B8%80%E6%9D%A1-SQL-%E6%9F%A5%E8%AF%A2%E8%AF%AD%E5%8F%A5%E7%9A%84%E6%89%A7%E8%A1%8C%E9%A1%BA%E5%BA%8F%EF%BC%9F"><span class="toc-text">1、一条 SQL 查询语句的执行顺序？</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#2%E3%80%81%E6%A3%80%E7%B4%A2%E6%95%B0%E6%8D%AE"><span class="toc-text">2、检索数据</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#3%E3%80%81%E8%BF%87%E6%BB%A4%E6%95%B0%E6%8D%AE"><span class="toc-text">3、过滤数据</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#4%E3%80%81%E6%B1%87%E6%80%BB%E6%95%B0%E6%8D%AE"><span class="toc-text">4、汇总数据</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#5%E3%80%81%E5%88%86%E7%BB%84%E6%95%B0%E6%8D%AE"><span class="toc-text">5、分组数据</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#6%E3%80%81%E4%BD%BF%E7%94%A8%E5%AD%90%E6%9F%A5%E8%AF%A2"><span class="toc-text">6、使用子查询</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#7%E3%80%81%E8%BF%9E%E6%8E%A5%E8%A1%A8"><span class="toc-text">7、连接表</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#8%E3%80%81%E7%BB%84%E5%90%88%E6%9F%A5%E8%AF%A2"><span class="toc-text">8、组合查询</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#9%E3%80%81%E5%A2%9E%E5%88%A0%E6%94%B9%E6%93%8D%E4%BD%9C"><span class="toc-text">9、增删改操作</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#10%E3%80%81%E8%A1%A8%E4%B8%8E%E7%B4%A2%E5%BC%95%E6%93%8D%E4%BD%9C"><span class="toc-text">10、表与索引操作</span></a></li></ol></div></div></widget>




</div>


    </aside>
    <div class='l_main'>
      

      



<div class="bread-nav fs12"><div id="breadcrumb"><a class="cap breadcrumb" href="/">主页</a><span class="sep"></span><a class="cap breadcrumb" href="/">文章</a><span class="sep"></span><a class="cap breadcrumb-link" href="/categories/%E6%95%B0%E6%8D%AE%E5%BA%93/">数据库</a></div><div id="post-meta">发布于&nbsp;<time datetime="2023-10-14T09:33:54.397Z">2023-10-14</time></div></div>

<article class='md-text content post'>
<h1 class="article-title"><span>SQL语法基础</span></h1>
<meta name="referrer" content="no-referrer"/>

<p>SQL语法基础练习与掌握</p>
<span id="more"></span>

<h3 id="1、一条-SQL-查询语句的执行顺序？"><a href="#1、一条-SQL-查询语句的执行顺序？" class="headerlink" title="1、一条 SQL 查询语句的执行顺序？"></a>1、一条 SQL 查询语句的执行顺序？</h3><div class="tag-plugin image"><div class="image-bg"><img src="https://cdn.tobebetterjavaer.com/tobebetterjavaer/images/sidebar/sanfene/mysql-47ddea92-cf8f-49c4-ab2e-69a829ff1be2.jpg" fancybox="true"/></div></div>

<ol>
<li><strong>FROM</strong>：对 FROM 子句中的左表<left_table>和右表<right_table>执行笛卡儿积（Cartesianproduct），产生虚拟表 VT1。</li>
<li><strong>ON</strong>：对虚拟表 VT1 应用 ON 筛选，只有那些符合<join_condition>的行才被插入虚拟表 VT2 中。</li>
<li><strong>JOIN</strong>：如果指定了 OUTER JOIN（如 LEFT OUTER JOIN、RIGHT OUTER JOIN），那么保留表中未匹配的行作为外部行添加到虚拟表 VT2 中，产生虚拟表 VT3。如果 FROM 子句包含两个以上表，则对上一个连接生成的结果表 VT3 和下一个表重复执行步骤 1）～步骤 3），直到处理完所有的表为止。</li>
<li><strong>WHERE</strong>：对虚拟表 VT3 应用 WHERE 过滤条件，只有符合<where_condition>的记录才被插入虚拟表 VT4 中</li>
<li><strong>GROUP BY</strong>：根据 GROUP BY 子句中的列，对 VT4 中的记录进行分组操作，产生 VT5。</li>
<li><strong>CUBE|ROLLUP</strong>：对表 VT5 进行 CUBE 或 ROLLUP 操作，产生表 VT6。</li>
<li><strong>HAVING</strong>：对虚拟表 VT6 应用 HAVING 过滤器，只有符合<having_condition>的记录才被插入虚拟表 VT7 中。</li>
<li><strong>SELECT</strong>：第二次执行 SELECT 操作，选择指定的列，插入到虚拟表 VT8 中。</li>
<li><strong>DISTINCT</strong>：去除重复数据，产生虚拟表 VT9。</li>
<li><strong>ORDER BY</strong>：将虚拟表 VT9 中的记录按照<order_by_list>进行排序操作，产生虚拟表 VT10。</li>
<li><strong>LIMIT</strong>：取出指定行的记录，产生虚拟表 VT11，并返回给查询用户。</li>
</ol>
<h3 id="2、检索数据"><a href="#2、检索数据" class="headerlink" title="2、检索数据"></a>2、检索数据</h3><p>（1）检索并列出所有已订购商品（<code>prod_id</code>）的去重后的清单。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="keyword">DISTINCT</span> prod_id</span><br><span class="line"><span class="keyword">FROM</span> OrderItems</span><br></pre></td></tr></table></figure>

<p>（2）从 <code>Orders</code> 表中检索顾客 ID（<code>cust_id</code>）和订单号（<code>order_num</code>），并先按顾客 ID 对结果进行排序，再按订单日期倒序排列。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"># 根据列名排序</span><br><span class="line"><span class="keyword">SELECT</span> cust_id, order_num</span><br><span class="line"><span class="keyword">FROM</span> Orders</span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> cust_id,order_date <span class="keyword">DESC</span></span><br><span class="line"># <span class="keyword">order</span> <span class="keyword">by</span> 对多列排序的时候，先排序的列放前面，后排序的列放后面。</span><br></pre></td></tr></table></figure>

<p>（3）显示 <code>OrderItems</code> 表中的数量（<code>quantity</code>）和价格（<code>item_price</code>），并按数量由多到少、价格由高到低排序。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> quantity, item_price</span><br><span class="line"><span class="keyword">FROM</span> OrderItems</span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> quantity <span class="keyword">DESC</span>,item_price <span class="keyword">DESC</span></span><br><span class="line"></span><br></pre></td></tr></table></figure>



<h3 id="3、过滤数据"><a href="#3、过滤数据" class="headerlink" title="3、过滤数据"></a>3、过滤数据</h3><p><code>WHERE</code> 可以过滤返回的数据。</p>
<p>下面的运算符可以在 <code>WHERE</code> 子句中使用：</p>
<table>
<thead>
<tr>
<th align="left">运算符</th>
<th align="left">描述</th>
</tr>
</thead>
<tbody><tr>
<td align="left">&#x3D;</td>
<td align="left">等于</td>
</tr>
<tr>
<td align="left">&lt;&gt;</td>
<td align="left">不等于。 <strong>注释：</strong> 在 SQL 的一些版本中，该操作符可被写成 !&#x3D;</td>
</tr>
<tr>
<td align="left">&gt;</td>
<td align="left">大于</td>
</tr>
<tr>
<td align="left">&lt;</td>
<td align="left">小于</td>
</tr>
<tr>
<td align="left">&gt;&#x3D;</td>
<td align="left">大于等于</td>
</tr>
<tr>
<td align="left">&lt;&#x3D;</td>
<td align="left">小于等于</td>
</tr>
<tr>
<td align="left">BETWEEN</td>
<td align="left">在某个范围内</td>
</tr>
<tr>
<td align="left">LIKE</td>
<td align="left">搜索某种模式</td>
</tr>
<tr>
<td align="left">IN</td>
<td align="left">指定针对某个列的多个可能值</td>
</tr>
</tbody></table>
<h3 id="4、汇总数据"><a href="#4、汇总数据" class="headerlink" title="4、汇总数据"></a>4、汇总数据</h3><p>汇总数据相关的函数：</p>
<table>
<thead>
<tr>
<th>函 数</th>
<th>说 明</th>
</tr>
</thead>
<tbody><tr>
<td><code>AVG()</code></td>
<td>返回某列的平均值</td>
</tr>
<tr>
<td><code>COUNT()</code></td>
<td>返回某列的行数</td>
</tr>
<tr>
<td><code>MAX()</code></td>
<td>返回某列的最大值</td>
</tr>
<tr>
<td><code>MIN()</code></td>
<td>返回某列的最小值</td>
</tr>
<tr>
<td><code>SUM()</code></td>
<td>返回某列值之和</td>
</tr>
</tbody></table>
<h3 id="5、分组数据"><a href="#5、分组数据" class="headerlink" title="5、分组数据"></a>5、分组数据</h3><p><code>HAVING</code> vs <code>WHERE</code>：</p>
<ul>
<li><code>WHERE</code>：过滤指定的行，后面不能加聚合函数（分组函数）。</li>
<li><code>HAVING</code>：过滤分组，必须要与 <code>GROUP BY</code> 连用，不能单独使用</li>
</ul>
<p>（1）返回订单数量总和不小于 100 的所有订单号，最后结果按照订单号升序排序。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> order_num</span><br><span class="line"><span class="keyword">FROM</span> OrderItems</span><br><span class="line"><span class="keyword">GROUP</span> <span class="keyword">BY</span> order_num</span><br><span class="line"><span class="keyword">HAVING</span> <span class="built_in">Sum</span>(quantity) <span class="operator">&gt;=</span> <span class="number">100</span></span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> order_num</span><br></pre></td></tr></table></figure>



<h3 id="6、使用子查询"><a href="#6、使用子查询" class="headerlink" title="6、使用子查询"></a>6、使用子查询</h3><p>（1）使用子查询，返回购买价格为 10 美元或以上产品的顾客列表，结果无需排序。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> cust_id</span><br><span class="line"><span class="keyword">FROM</span> Orders</span><br><span class="line"><span class="keyword">WHERE</span> order_num <span class="keyword">IN</span> (<span class="keyword">SELECT</span> <span class="keyword">DISTINCT</span> order_num</span><br><span class="line">    <span class="keyword">FROM</span> OrderItems</span><br><span class="line">    <span class="keyword">where</span> item_price <span class="operator">&gt;=</span> <span class="number">10</span>)</span><br></pre></td></tr></table></figure>



<h3 id="7、连接表"><a href="#7、连接表" class="headerlink" title="7、连接表"></a>7、连接表</h3><p>连接表时需要在每个表中选择一个字段，并对这些字段的值进行比较，值相同的两条记录将合并为一条。<strong>连接表的本质就是将不同表的记录合并起来，形成一张新表。当然，这张新表只是临时的，它仅存在于本次查询期间</strong>。</p>
<p>使用 <code>JOIN</code> 连接两个表的基本语法如下：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> table1.column1, table2.column2...</span><br><span class="line"><span class="keyword">FROM</span> table1</span><br><span class="line"><span class="keyword">JOIN</span> table2</span><br><span class="line"><span class="keyword">ON</span> table1.common_column1 <span class="operator">=</span> table2.common_column2;</span><br></pre></td></tr></table></figure>



<p>SQL 允许在 <code>JOIN</code> 左边加上一些修饰性的关键词，从而形成不同类型的连接，如下表所示：</p>
<table>
<thead>
<tr>
<th>连接类型</th>
<th>说明</th>
</tr>
</thead>
<tbody><tr>
<td>INNER JOIN 内连接</td>
<td>（默认连接方式）只有当两个表都存在满足条件的记录时才会返回行。</td>
</tr>
<tr>
<td>LEFT JOIN &#x2F; LEFT OUTER JOIN 左(外)连接</td>
<td>返回左表中的所有行，即使右表中没有满足条件的行也是如此。</td>
</tr>
<tr>
<td>RIGHT JOIN &#x2F; RIGHT OUTER JOIN 右(外)连接</td>
<td>返回右表中的所有行，即使左表中没有满足条件的行也是如此。</td>
</tr>
<tr>
<td>FULL JOIN &#x2F; FULL OUTER JOIN 全(外)连接</td>
<td>只要其中有一个表存在满足条件的记录，就返回行。</td>
</tr>
<tr>
<td>SELF JOIN</td>
<td>将一个表连接到自身，就像该表是两个表一样。为了区分两个表，在 SQL 语句中需要至少重命名一个表。</td>
</tr>
<tr>
<td>CROSS JOIN</td>
<td>交叉连接，从两个或者多个连接表中返回记录集的笛卡尔积。</td>
</tr>
</tbody></table>
<p>如果不加任何修饰词，只写 <code>JOIN</code>，那么默认为 <code>INNER JOIN</code>。</p>
<h3 id="8、组合查询"><a href="#8、组合查询" class="headerlink" title="8、组合查询"></a>8、组合查询</h3><p><code>UNION</code> 运算符将两个或更多查询的结果组合起来，并生成一个结果集，其中包含来自 <code>UNION</code> 中参与查询的提取行。</p>
<p><code>UNION</code> 基本规则：</p>
<ul>
<li>所有查询的列数和列顺序必须相同。</li>
<li>每个查询中涉及表的列的数据类型必须相同或兼容。</li>
<li>通常返回的列名取自第一个查询。</li>
</ul>
<p>默认地，<code>UNION</code> 操作符选取不同的值。如果允许重复的值，请使用 <code>UNION ALL</code>。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> column_name(s) <span class="keyword">FROM</span> table1</span><br><span class="line"><span class="keyword">UNION</span> <span class="keyword">ALL</span></span><br><span class="line"><span class="keyword">SELECT</span> column_name(s) <span class="keyword">FROM</span> table2;</span><br></pre></td></tr></table></figure>

<p><code>UNION</code> 结果集中的列名总是等于 <code>UNION</code> 中第一个 <code>SELECT</code> 语句中的列名。</p>
<p><code>JOIN</code> vs <code>UNION</code>：</p>
<ul>
<li><code>JOIN</code> 中连接表的列可能不同，但在 <code>UNION</code> 中，所有查询的列数和列顺序必须相同。</li>
<li><code>UNION</code> 将查询之后的行放在一起（垂直放置），但 <code>JOIN</code> 将查询之后的列放在一起（水平放置），即它构成一个笛卡尔积。</li>
</ul>
<h3 id="9、增删改操作"><a href="#9、增删改操作" class="headerlink" title="9、增删改操作"></a>9、增删改操作</h3><p>SQL 插入记录的方式汇总：</p>
<ul>
<li><strong>普通插入（全字段）</strong> ：<code>INSERT INTO table_name VALUES (value1, value2, ...)</code></li>
<li><strong>普通插入（限定字段）</strong> ：<code>INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...)</code></li>
<li><strong>多条一次性插入</strong> ：<code>INSERT INTO table_name (column1, column2, ...) VALUES (value1_1, value1_2, ...), (value2_1, value2_2, ...), ...</code></li>
<li><strong>从另一个表导入</strong> ：<code>INSERT INTO table_name SELECT * FROM table_name2 [WHERE key=value]</code></li>
<li><strong>带更新的插入</strong> ：<code>REPLACE INTO table_name VALUES (value1, value2, ...)</code>（注意这种原理是检测到主键或唯一性索引键重复就删除原记录后重新插入）</li>
</ul>
<p>更新记录：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">UPDATE</span> examination_info <span class="keyword">SET</span> tag <span class="operator">=</span> <span class="string">&#x27;Python&#x27;</span> <span class="keyword">WHERE</span> tag<span class="operator">=</span><span class="string">&#x27;PYTHON&#x27;</span></span><br></pre></td></tr></table></figure>

<p>或</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">UPDATE</span> examination_info</span><br><span class="line"><span class="keyword">SET</span> tag <span class="operator">=</span> REPLACE(tag,<span class="string">&#x27;PYTHON&#x27;</span>,<span class="string">&#x27;Python&#x27;</span>)</span><br><span class="line"></span><br><span class="line"># REPLACE (目标字段，&quot;查找内容&quot;,&quot;替换内容&quot;)</span><br></pre></td></tr></table></figure>



<p>删除记录：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">DELETE</span> <span class="keyword">FROM</span> exam_record <span class="keyword">WHERE</span> <span class="keyword">MINUTE</span> (TIMEDIFF(submit_time , start_time)) <span class="operator">&lt;</span> <span class="number">5</span> <span class="keyword">AND</span> score <span class="operator">&lt;</span> <span class="number">60</span></span><br></pre></td></tr></table></figure>



<h3 id="10、表与索引操作"><a href="#10、表与索引操作" class="headerlink" title="10、表与索引操作"></a>10、表与索引操作</h3><p>创建表</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> IF <span class="keyword">NOT</span> <span class="keyword">EXISTS</span> user_info_vip(</span><br><span class="line">    id <span class="type">INT</span>(<span class="number">11</span>) <span class="keyword">PRIMARY</span> KEY AUTO_INCREMENT COMMENT<span class="string">&#x27;自增ID&#x27;</span>,</span><br><span class="line">    uid <span class="type">INT</span>(<span class="number">11</span>) <span class="keyword">UNIQUE</span> <span class="keyword">NOT</span> <span class="keyword">NULL</span> COMMENT <span class="string">&#x27;用户ID&#x27;</span>,</span><br><span class="line">    nick_name <span class="type">VARCHAR</span>(<span class="number">64</span>) COMMENT<span class="string">&#x27;昵称&#x27;</span>,</span><br><span class="line">    achievement <span class="type">INT</span>(<span class="number">11</span>) <span class="keyword">DEFAULT</span> <span class="number">0</span> COMMENT <span class="string">&#x27;成就值&#x27;</span>,</span><br><span class="line">    `level` <span class="type">INT</span>(<span class="number">11</span>) COMMENT <span class="string">&#x27;用户等级&#x27;</span>,</span><br><span class="line">    job <span class="type">VARCHAR</span>(<span class="number">32</span>) COMMENT <span class="string">&#x27;职业方向&#x27;</span>,</span><br><span class="line">    register_time DATETIME <span class="keyword">DEFAULT</span> <span class="built_in">CURRENT_TIMESTAMP</span> COMMENT <span class="string">&#x27;注册时间&#x27;</span></span><br><span class="line">)<span class="type">CHARACTER</span> <span class="keyword">SET</span> UTF8</span><br></pre></td></tr></table></figure>

<p>修改表</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> user_info</span><br><span class="line">    <span class="keyword">ADD</span> school <span class="type">VARCHAR</span>(<span class="number">15</span>) AFTER level,</span><br><span class="line">    CHANGE job profession <span class="type">VARCHAR</span>(<span class="number">10</span>),</span><br><span class="line">    MODIFY achievement <span class="type">INT</span>(<span class="number">11</span>) <span class="keyword">DEFAULT</span> <span class="number">0</span>;</span><br></pre></td></tr></table></figure>

<p>删除表</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">DROP</span> <span class="keyword">TABLE</span> IF <span class="keyword">EXISTS</span> exam_record_2011;</span><br></pre></td></tr></table></figure>



<p>创建索引</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 示例：</span></span><br><span class="line"><span class="comment">-- 添加B-Tree索引：</span></span><br><span class="line">	<span class="keyword">CREATE</span> INDEX idx_name(索引名) <span class="keyword">ON</span> 表名 (字段名);   <span class="comment">-- idx_name为索引名，以下都是</span></span><br><span class="line"><span class="comment">-- 创建唯一索引：</span></span><br><span class="line">	<span class="keyword">CREATE</span> <span class="keyword">UNIQUE</span> INDEX idx_name <span class="keyword">ON</span> 表名 (字段名);</span><br><span class="line"><span class="comment">-- 创建一个主键索引：</span></span><br><span class="line">	<span class="keyword">ALTER</span> <span class="keyword">TABLE</span> 表名 <span class="keyword">ADD</span> <span class="keyword">PRIMARY</span> KEY (字段名);</span><br><span class="line"><span class="comment">-- 创建一个全文索引</span></span><br><span class="line">	<span class="keyword">ALTER</span> <span class="keyword">TABLE</span> 表名 <span class="keyword">ADD</span> FULLTEXT INDEX idx_name (字段名);</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 通过以上示例，可以看出create 和 alter 都可以添加索引</span></span><br><span class="line"></span><br></pre></td></tr></table></figure>

<p>示例：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> examination_info</span><br><span class="line">    <span class="keyword">ADD</span> INDEX idx_duration(duration),</span><br><span class="line">    <span class="keyword">ADD</span> <span class="keyword">UNIQUE</span> INDEX uniq_idx_exam_id(exam_id),</span><br><span class="line">    <span class="keyword">ADD</span> FULLTEXT INDEX full_idx_tag(tag);</span><br></pre></td></tr></table></figure>



<p>删除索引</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 使用 DROP INDEX 删除索引</span></span><br><span class="line"><span class="keyword">DROP</span> INDEX idx_name <span class="keyword">ON</span> 表名;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 使用 ALTER TABLE 删除索引</span></span><br><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> employees <span class="keyword">DROP</span> INDEX idx_email;</span><br></pre></td></tr></table></figure>

<blockquote>
<p>注：在 MySQL 中，一次删除多个索引的操作是不支持的。每次删除索引时，只能指定一个索引名称进行删除。</p>
</blockquote>
<p>示例：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">DROP</span> INDEX uniq_idx_exam_id <span class="keyword">ON</span> examination_info;</span><br><span class="line"><span class="keyword">DROP</span> INDEX full_idx_tag <span class="keyword">ON</span> examination_info;</span><br></pre></td></tr></table></figure>






</article>

<div class="related-wrap reveal" id="read-next"><section class="body"><div class="item" id="prev"><div class="note">较新文章</div><a href="/2023/10/15/%E5%8D%8E%E5%8D%97%E7%A7%8B%E6%A0%91%E5%9B%BE%E9%89%B4/">华南秋树图鉴</a></div><div class="item" id="next"><div class="note">较早文章</div><a href="/2023/10/09/%E9%9D%A2%E8%AF%95%E9%A2%98%E7%B2%BE%E9%80%89%EF%BC%88%E4%BA%8C%EF%BC%89/">面试题精选（二）</a></div></section></div>






  <div class='related-wrap md-text reveal' id="comments">
    <section class='header cmt-title cap theme'>
      快来参与讨论吧
    </section>
    <section class='body cmt-body giscus'>
      

<svg class="loading" style="vertical-align: middle;fill: currentColor;overflow: hidden;" viewBox="0 0 1024 1024" version="1.1" xmlns="http://www.w3.org/2000/svg" p-id="2709"><path d="M832 512c0-176-144-320-320-320V128c211.2 0 384 172.8 384 384h-64zM192 512c0 176 144 320 320 320v64C300.8 896 128 723.2 128 512h64z" p-id="2710"></path></svg>

<div id="giscus" data-repo="echoalways/giscus" data-repo-id="R_kgDOKjJRiQ" data-category="Announcements" data-category-id="DIC_kwDOKjJRic4CaUxu" data-mapping="pathname" data-strict="0" data-reactions-enabled="1" data-emit-metadata="0" data-input-position="top" data-theme="preferred_color_scheme" data-lang="zh-CN" data-loading="lazy" crossorigin="anonymous"></div>

    </section>
  </div>



      
<footer class="page-footer reveal fs12"><hr><div class="text" style="text-align:center;"><p>云无心以出岫@远岫♥</p>
<div><span id="timeDate">载入天数...</span><span id="times">载入时分秒...</span>，<span id="busuanzi_container_site_pv">总访问量: <span id="busuanzi_value_site_pv"></span>次</span>，<span id="busuanzi_container_site_uv">访客数: <span id="busuanzi_value_site_uv"></span>人</span></div></div></footer>

<script async src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>
<script>
  function createtime() {
    var now = new Date();
    var grt= new Date("10/03/2023 18:40:00");
    now.setTime(now.getTime()+250);
    days = (now - grt ) / 1000 / 60 / 60 / 24; dnum = Math.floor(days);
    hours = (now - grt ) / 1000 / 60 / 60 - (24 * dnum); hnum = Math.floor(hours);
    if(String(hnum).length ==1 ){hnum = "0" + hnum;} minutes = (now - grt ) / 1000 /60 - (24 * 60 * dnum) - (60 * hnum);
    mnum = Math.floor(minutes); if(String(mnum).length ==1 ){mnum = "0" + mnum;}
    seconds = (now - grt ) / 1000 - (24 * 60 * 60 * dnum) - (60 * 60 * hnum) - (60 * mnum);
    snum = Math.round(seconds); if(String(snum).length ==1 ){snum = "0" + snum;}
    document.getElementById("timeDate").innerHTML = "本站已运行 "+dnum+" 天 ";
    document.getElementById("times").innerHTML = hnum + " 小时 " + mnum + " 分 " + snum + " 秒";
  };
  setInterval("createtime()",250);
</script>

      <div class='float-panel mobile-only blur' style='display:none'>
  <button type='button' class='sidebar-toggle mobile' onclick='sidebar.toggle()'>
    <svg class="icon" style="width: 1em; height: 1em;vertical-align: middle;fill: currentColor;overflow: hidden;" viewBox="0 0 1024 1024" version="1.1" xmlns="http://www.w3.org/2000/svg" p-id="15301"><path d="M566.407 808.3c26.9-0.1 49.3-20.8 51.6-47.6-1.9-27.7-23.9-49.7-51.6-51.6h-412.6c-28.2-1.4-52.6 19.5-55.5 47.6 2.3 26.8 24.6 47.5 51.6 47.6h416.5v4z m309.3-249.9c26.9-0.1 49.3-20.8 51.6-47.6-2.2-26.8-24.6-47.5-51.6-47.6h-721.9c-27.7-2.8-52.5 17.4-55.3 45.1-0.1 0.8-0.1 1.7-0.2 2.5 0.9 27.2 23.6 48.5 50.7 47.6H875.707z m-103.1-245.9c26.9-0.1 49.3-20.8 51.6-47.6-0.4-28.3-23.2-51.1-51.5-51.6h-618.9c-29.5-1.1-54.3 21.9-55.5 51.4v0.2c1.4 27.8 25.2 49.2 53 47.8 0.8 0 1.7-0.1 2.5-0.2h618.8z" p-id="15302"></path><path d="M566.407 808.3c26.9-0.1 49.3-20.8 51.6-47.6-1.9-27.7-23.9-49.7-51.6-51.6h-412.6c-28.2-1.4-52.6 19.5-55.5 47.6 1.9 27.7 23.9 49.7 51.6 51.6h416.5z m309.3-249.9c26.9-0.1 49.3-20.8 51.6-47.6-2.2-26.8-24.6-47.5-51.6-47.6h-721.9c-27.7-2.8-52.5 17.4-55.3 45.1-0.1 0.8-0.1 1.7-0.2 2.5 0.9 27.2 23.6 48.5 50.7 47.6H875.707z m-103.1-245.9c26.9-0.1 49.3-20.8 51.6-47.6-0.4-28.3-23.2-51.1-51.5-51.6h-618.9c-29.5-1.1-54.3 21.9-55.5 51.4v0.2c1.4 27.8 25.2 49.2 53 47.8 0.8 0 1.7-0.1 2.5-0.2h618.8z" p-id="15303"></path></svg>
  </button>
</div>

    </div>
  </div>
  <div class='scripts'>
    <script type="text/javascript">
  const stellar = {
    // 懒加载 css https://github.com/filamentgroup/loadCSS
    loadCSS: (href, before, media, attributes) => {
      var doc = window.document;
      var ss = doc.createElement("link");
      var ref;
      if (before) {
        ref = before;
      } else {
        var refs = (doc.body || doc.getElementsByTagName("head")[0]).childNodes;
        ref = refs[refs.length - 1];
      }
      var sheets = doc.styleSheets;
      if (attributes) {
        for (var attributeName in attributes) {
          if (attributes.hasOwnProperty(attributeName)) {
            ss.setAttribute(attributeName, attributes[attributeName]);
          }
        }
      }
      ss.rel = "stylesheet";
      ss.href = href;
      ss.media = "only x";
      function ready(cb) {
        if (doc.body) {
          return cb();
        }
        setTimeout(function () {
          ready(cb);
        });
      }
      ready(function () {
        ref.parentNode.insertBefore(ss, before ? ref : ref.nextSibling);
      });
      var onloadcssdefined = function (cb) {
        var resolvedHref = ss.href;
        var i = sheets.length;
        while (i--) {
          if (sheets[i].href === resolvedHref) {
            return cb();
          }
        }
        setTimeout(function () {
          onloadcssdefined(cb);
        });
      };
      function loadCB() {
        if (ss.addEventListener) {
          ss.removeEventListener("load", loadCB);
        }
        ss.media = media || "all";
      }
      if (ss.addEventListener) {
        ss.addEventListener("load", loadCB);
      }
      ss.onloadcssdefined = onloadcssdefined;
      onloadcssdefined(loadCB);
      return ss;
    },

    // 从 butterfly 和 volantis 获得灵感
    loadScript: (src, opt) => new Promise((resolve, reject) => {
      var script = document.createElement('script');
      if (src.startsWith('/')){
        src = stellar.config.root + src.substring(1);
      }
      script.src = src;
      if (opt) {
        for (let key of Object.keys(opt)) {
          script[key] = opt[key]
        }
      } else {
        // 默认异步，如果需要同步，第二个参数传入 {} 即可
        script.async = true
      }
      script.onerror = reject
      script.onload = script.onreadystatechange = function() {
        const loadState = this.readyState
        if (loadState && loadState !== 'loaded' && loadState !== 'complete') return
        script.onload = script.onreadystatechange = null
        resolve()
      }
      document.head.appendChild(script)
    }),

    // https://github.com/jerryc127/hexo-theme-butterfly
    jQuery: (fn) => {
      if (typeof jQuery === 'undefined') {
        stellar.loadScript(stellar.plugins.jQuery).then(fn)
      } else {
        fn()
      }
    }
  };
  stellar.version = '1.19.0';
  stellar.github = 'https://github.com/xaoxuu/hexo-theme-stellar/tree/1.19.0';
  stellar.config = {
    date_suffix: {
      just: '刚刚',
      min: '分钟前',
      hour: '小时前',
      day: '天前',
      month: '个月前',
    },
    root : '/',
  };

  // required plugins (only load if needs)
  stellar.plugins = {
    jQuery: 'https://gcore.jsdelivr.net/npm/jquery@3.6.2/dist/jquery.min.js'
  };

  if ('local_search') {
    stellar.search = {};
    stellar.search.service = 'local_search';
    if (stellar.search.service == 'local_search') {
      let service_obj = Object.assign({}, {"field":"all","path":"/search.json","content":true,"sort":"-date"});
      stellar.search[stellar.search.service] = service_obj;
    }
  }

  // stellar js
  stellar.plugins.stellar = Object.assign({"sites":"/js/plugins/sites.js","friends":"/js/plugins/friends.js","ghinfo":"/js/plugins/ghinfo.js","timeline":"/js/plugins/timeline.js","linkcard":"/js/plugins/linkcard.js","fcircle":"/js/plugins/fcircle.js","weibo":"/js/plugins/weibo.js"});

  stellar.plugins.marked = Object.assign("https://cdn.bootcdn.net/ajax/libs/marked/4.0.18/marked.min.js");
  // optional plugins
  if ('false' == 'true') {
    stellar.plugins.lazyload = Object.assign({"enable":false,"js":"https://gcore.jsdelivr.net/npm/vanilla-lazyload@17.8.3/dist/lazyload.min.js","transition":"blur"});
  }
  if ('true' == 'true') {
    stellar.plugins.swiper = Object.assign({"enable":true,"css":"https://unpkg.com/swiper@8.4.5/swiper-bundle.min.css","js":"https://unpkg.com/swiper@8.4.5/swiper-bundle.min.js"});
  }
  if ('' == 'true') {
    stellar.plugins.scrollreveal = Object.assign({"enable":null,"js":"https://gcore.jsdelivr.net/npm/scrollreveal@4.0.9/dist/scrollreveal.min.js","distance":"8px","duration":500,"interval":100,"scale":1});
  }
  if ('true' == 'true') {
    stellar.plugins.preload = Object.assign({"enable":true,"service":"flying_pages","instant_page":"https://gcore.jsdelivr.net/gh/volantis-x/cdn-volantis@4.1.2/js/instant_page.js","flying_pages":"https://gcore.jsdelivr.net/gh/gijo-varghese/flying-pages@2.1.2/flying-pages.min.js"});
  }
  if ('true' == 'true') {
    stellar.plugins.fancybox = Object.assign({"enable":true,"js":"https://gcore.jsdelivr.net/npm/@fancyapps/ui@4.0/dist/fancybox.umd.js","css":"https://gcore.jsdelivr.net/npm/@fancyapps/ui@4.0/dist/fancybox.css","selector":".swiper-slide img"});
  }
  if ('false' == 'true') {
    stellar.plugins.heti = Object.assign({"enable":false,"css":"https://unpkg.com/heti@0.9.2/umd/heti.min.css","js":"https://unpkg.com/heti@0.9.2/umd/heti-addon.min.js"});
  }
  if ('true' == 'true') {
    stellar.plugins.copycode = Object.assign({"enable":true,"js":"/js/plugins/copycode.js","default_text":"Copy","success_text":"Copied"});
  }
</script>

<!-- required -->

  
<script src="/js/main.js" async></script>



<!-- optional -->

  <script>
  function loadJS() {
    const els = document.querySelectorAll("#comments #giscus");
    if (els.length === 0) return;
    els.forEach((el, i) => {
      try {
        el.innerHTML = '';
      } catch (error) {
        console.log(error);
      }
      var script = document.createElement('script');
      script.src = 'https://giscus.app/client.js';
      script.async = true;
      for (let key of Object.keys(el.attributes)) {
        let attr = el.attributes[key];
        if (['class', 'id'].includes(attr.name) === false) {
          script.setAttribute(attr.name, attr.value);
        }
      }
      el.appendChild(script);
    });
  }
  window.addEventListener('DOMContentLoaded', (event) => {
    loadJS();
  });
</script>




<!-- inject -->


  </div>
</body>
</html>
